package edu.jhu.secondhome.database.accessors;

import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.ArrayList;
import java.util.Date;
import java.util.List;

import edu.jhu.secondhome.database.ConnectionPool;
import edu.jhu.secondhome.database.DataUtil;
import edu.jhu.secondhome.database.beans.Room;

public class RoomAccessor extends Accessor {
	
	/**
	 * Gets all rooms from the database
	 * @return List<Room>
	 * @throws SQLException
	 */
	public List<Room> getRooms() throws SQLException {
		List<Room> rooms = new ArrayList<Room>();
		
		final ConnectionPool pool = ConnectionPool.getInstance();
		final Connection conn = pool.getConnection();
		PreparedStatement ps = null;
		
		try {
			ps = conn.prepareStatement("SELECT * FROM Rooms");
			ResultSet rs = ps.executeQuery();
			// Move to first result:
			rs.next();
			// Process results
			while (!rs.isAfterLast()) {
				rooms.add(extractRoom(rs));
				rs.next();
			}
		} finally {
			DataUtil.closeStatement(ps);
			pool.freeConnection(conn);
		}
		return rooms;
	}
	
	/**
	 * Gets all rooms from the database for a hotel
	 * @return List<Room>
	 * @throws SQLException
	 */
	public List<Room> getRoomsForHotel(final String hotelName) throws SQLException {
		List<Room> rooms = new ArrayList<Room>();
		
		final ConnectionPool pool = ConnectionPool.getInstance();
		final Connection conn = pool.getConnection();
		PreparedStatement ps = null;
		
		try {
			ps = conn.prepareStatement("SELECT * FROM Rooms where hotelName = ?");
			ps.setString(1, hotelName);
			ResultSet rs = ps.executeQuery();
			// Move to first result:
			rs.next();
			// Process results
			while (!rs.isAfterLast()) {
				rooms.add(extractRoom(rs));
				rs.next();
			}
		} finally {
			DataUtil.closeStatement(ps);
			pool.freeConnection(conn);
		}
		return rooms;
	}
	
	public Room getRoom(final String hotelName, final String roomNumber) throws SQLException
	{
	  final ConnectionPool pool = ConnectionPool.getInstance();
	  final Connection conn = pool.getConnection();
	  PreparedStatement ps = null;
	  
	  try {
	    ps = conn.prepareStatement("SELECT * FROM Rooms where hotelName = ? AND roomNumber = ?");
	    ps.setString(1, hotelName);
	    ps.setString(2, roomNumber);
	    ResultSet rs = ps.executeQuery();
	    // Move to first result:
	    rs.next();
	    // Process results
	    if (!rs.isAfterLast())
	    {
	      return extractRoom(rs);
	    }
	  } finally {
	    DataUtil.closeStatement(ps);
	    pool.freeConnection(conn);
	  }
	  
	  return null;
	}
	
	/**
	 * Gets all rooms from the database for a hotel with capacity
	 * @return List<Room>
	 * @throws SQLException
	 */
	public List<Room> getRoomsForHotelWithCapacity(final String hotelName, 
			final int numOfPeople) throws SQLException {
		List<Room> rooms = new ArrayList<Room>();
		
		final ConnectionPool pool = ConnectionPool.getInstance();
		final Connection conn = pool.getConnection();
		PreparedStatement ps = null;
		
		try {
			ps = conn.prepareStatement("SELECT * FROM Rooms WHERE " +
					"hotelName = ? AND occupancy >= ?");
			ps.setString(1, hotelName);
			ps.setInt(2, numOfPeople);
			ResultSet rs = ps.executeQuery();
			// Move to first result:
			rs.next();
			// Process results
			while (!rs.isAfterLast()) {
				rooms.add(extractRoom(rs));
				rs.next();
			}
		} finally {
			DataUtil.closeStatement(ps);
			pool.freeConnection(conn);
		}
		return rooms;
	}
	
	public boolean isRoomAvailableForDuration(String hotelName, String roomNumber,
	    Date startDate, int numOfDays ) throws SQLException
	{
	   final ConnectionPool pool = ConnectionPool.getInstance();
	    final Connection conn = pool.getConnection();
	    PreparedStatement ps = null;
	  
	    try {
	      
	      //Returns the roomNumber if the room is available for the given date range.
//	      SELECT roomNumber FROM Rooms WHERE roomNumber='100' AND roomNumber NOT IN (
//	      SELECT roomNumber FROM Reservations WHERE hotelName='The Flag Hotel' AND
//	      ((startDate BETWEEN '2014-12-18' AND DATE_ADD('2014-12-18', INTERVAL '6' DAY))
//	          OR (DATE_ADD(startDate, INTERVAL (numOfDays-1) DAY) BETWEEN '2014-12-18' AND DATE_ADD('2014-12-18', INTERVAL '6' DAY ))));

	      ps = conn.prepareStatement( "SELECT roomNumber " +
	          "FROM Rooms " +
	          "WHERE roomNumber = ? " +
	          "AND roomNumber NOT IN (" +
	          "SELECT roomNumber " +
	            "FROM Reservations " + 
	            "WHERE hotelName = ? " +
	            "AND ((startDate BETWEEN ? AND DATE_ADD(?, INTERVAL ? DAY)) " +
	            "OR (DATE_ADD(startDate, INTERVAL (numOfDays-1) DAY) BETWEEN ? AND DATE_ADD(?, INTERVAL ? DAY ))))");
	      
	      java.sql.Date sqlStart = new java.sql.Date( startDate.getTime() );
	      ps.setString(1, roomNumber);
	      ps.setString(2, hotelName);
	      ps.setDate(3, sqlStart);
	      ps.setDate(4, sqlStart);
	      ps.setInt(5, numOfDays - 1);
	      ps.setDate(6, sqlStart);
	      ps.setDate(7, sqlStart);
	      ps.setInt(8, numOfDays - 1);
	      
	      System.out.println("SELECT roomNumber " +
            "FROM Rooms " +
            "WHERE roomNumber = "+roomNumber+" " +
            "AND roomNumber NOT IN (" +
            "SELECT roomNumber " +
              "FROM Reservations " + 
              "WHERE hotelName = "+hotelName+" " +
              "AND ((startDate BETWEEN "+sqlStart+" AND DATE_ADD("+sqlStart+", INTERVAL "+(numOfDays - 1)+" DAY)) " +
              "OR (DATE_ADD(startDate, INTERVAL (numOfDays-1) DAY) BETWEEN "+sqlStart+" AND DATE_ADD("+sqlStart+", INTERVAL "+ (numOfDays - 1)+" DAY ))))");
	      
	      ResultSet rs = ps.executeQuery();
	      // Move to first result:
	      return rs.isBeforeFirst();
	    } finally {
	      DataUtil.closeStatement(ps);
	      pool.freeConnection(conn);
	    }
	}
	
	/**
	 * Gets all rooms from the database for a hotel with capacity free in the date range
	 * @return List<Room>
	 * @throws SQLException
	 */
	public List<Room> getRoomsForHotelWithCapacityFreeOnDate(final String hotelName, 
			final int numOfPeople, final Date date, final int numOfDays) throws SQLException {
		List<Room> rooms = new ArrayList<Room>();
		
		final ConnectionPool pool = ConnectionPool.getInstance();
		final Connection conn = pool.getConnection();
		PreparedStatement ps = null;
		
		try {
			/*ps = conn.prepareStatement("SELECT Rooms.hotelName, " +
					"Rooms.roomNumber, Rooms.occupancy, " +
					"Rooms.costPerNight FROM Rooms, Reservations " +
					"WHERE Rooms.hotelName = ? AND Reservations.hotelName = ? AND " +
					"Rooms.occupancy >= ? AND " +
					"Reservations.startDate NOT BETWEEN ? AND DATE_ADD(?, INTERVAL ? DAY) AND " +
					"DATE_ADD(Reservations.startDate, INTERVAL Reservations.numOfDays DAY) " +
					"NOT BETWEEN ? AND DATE_ADD(?, INTERVAL ? DAY) AND " +
					"!(Reservations.startDate < ? AND " +
					"DATE_ADD(Reservations.startDate, INTERVAL Reservations.numOfDays DAY) > " +
					"DATE_ADD(?, INTERVAL ? DAY))");*/
			ps = conn.prepareStatement("SELECT hotelName, roomNumber, occupancy, costPerNight " +
					"FROM Rooms " +
					"WHERE hotelName = ? AND " +
					"occupancy >= ? AND " +
					"(roomNumber NOT IN (" +
						"SELECT roomNumber " +
						"FROM Reservations " +
						"WHERE NOT " +
						"((startDate NOT BETWEEN " +
							"CAST(? AS DATE) AND DATE_ADD(CAST(? AS DATE), INTERVAL ? DAY)) AND " +
						"(DATE_ADD(startDate, INTERVAL (numOfDays-1) DAY) NOT BETWEEN " +
							"CAST(? AS DATE) AND DATE_ADD(CAST(? AS DATE), INTERVAL ? DAY)) AND " +
						"NOT ((startDate < CAST(? AS DATE)) AND " +
						"(DATE_ADD(startDate, INTERVAL (numOfDays-1) DAY) > DATE_ADD(CAST(? AS DATE), INTERVAL ? DAY))))" +
					"))");
			
			java.sql.Date sqlStart = new java.sql.Date(date.getTime());
			ps.setString(1, hotelName);
			ps.setInt(2, numOfPeople);
			ps.setDate(3, sqlStart);
			ps.setDate(4, sqlStart);
			ps.setInt(5, numOfDays - 1);
			ps.setDate(6, sqlStart);
			ps.setDate(7, sqlStart);
			ps.setInt(8, numOfDays - 1);
			ps.setDate(9, sqlStart);
			ps.setDate(10, sqlStart);
			ps.setInt(11, numOfDays - 1);
			
			ResultSet rs = ps.executeQuery();
			// Move to first result:
			rs.next();
			// Process results
			while (!rs.isAfterLast()) {
				rooms.add(extractRoom(rs));
				rs.next();
			}
		} finally {
			DataUtil.closeStatement(ps);
			pool.freeConnection(conn);
		}
		return rooms;
	}
	
	/**
	 * Inserts a new room into the database
	 * @param room - bean filled with the information to add
	 * @return int (from PreparedStatement: the row count for SQL Data 
	 * 			Manipulation Language (DML) statements or 0 for statements 
	 * 			that return nothing)
	 * @throws SQLException
	 */
	public int insertRoom(Room room) throws SQLException {
		String sql = "INSERT INTO Rooms (hotelName, roomNumber, occupancy, costPerNight" + 
			") VALUES (?, ?, ?, ?)";
		
		Object[] params = {
				room.getHotelName(),
				room.getRoomNumber(),
				room.getOccupancy(),
				room.getCostPerNight()
		};
		
		return executeUpdateStatement(sql, params);
	}
	
	/**
	 * Updates the room information.
	 * @param room - bean filled with information to update the room
	 * @return int (from PreparedStatement: the row count for SQL Data 
	 * 			Manipulation Language (DML) statements or 0 for statements 
	 * 			that return nothing)
	 * @throws SQLException
	 */
	public int updateRoom(Room room) throws SQLException {
		String sql = "UPDATE Rooms SET " +
			"occupancy = ?, costPerNight = ? " + 
			"WHERE hotelName = ? AND roomNumber = ?";
		
		Object[] params = {
				room.getOccupancy(),
				room.getCostPerNight(), 
				room.getHotelName(),
				room.getRoomNumber()
		};
		
		return executeUpdateStatement(sql, params);
	}
	
	/**
	 * Deletes a room from the database
	 * @param room - bean with the hotel name and room number of the room to delete
	 * @return int (from PreparedStatement: the row count for SQL Data 
	 * 			Manipulation Language (DML) statements or 0 for statements 
	 * 			that return nothing)
	 * @throws SQLException
	 */
	public int deleteRoom(Room room) throws SQLException {
		return deleteRoom(room.getHotelName(), room.getRoomNumber());
	}
	
	/**
	 * Deletes a room from the database based on the hotelName and roomNumber
	 * @param hotelName
	 * @param roomNumber
	 * @return int (from PreparedStatement: the row count for SQL Data 
	 * 			Manipulation Language (DML) statements or 0 for statements 
	 * 			that return nothing)
	 * @throws SQLException
	 */
	public int deleteRoom(final String hotelName, final String roomNumber) throws SQLException {
		String sql = "DELETE FROM Rooms " + 
			"WHERE hotelName = ? AND roomNumber = ?";
		
		String[] params = { hotelName, roomNumber };
		
		return executeUpdateStatement(sql, params);
	}
	
	
	/**
	 * Helper method to extract the room information from the ResultSet 
	 * row and put it into the bean
	 * @param rs - ResultSet pointing to the row of data to pull from
	 * @return populated Room object
	 * @throws SQLException
	 */
	private Room extractRoom(ResultSet rs) throws SQLException {
		return new Room(rs.getString("hotelName"), rs.getString("roomNumber"), 
				rs.getString("occupancy"), rs.getInt("costPerNight"));
	}
}
